<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class M_student_subjects extends MY_Model
{
	private $__table = 'student_subjects';
	protected $_table = 'student_subjects';
	protected $_timestamp = FALSE;
	
	public function generate_student_subjects($enrollment_id, $level_id)
	{
	  
	  $sql = 'SELECT subject_id 
			  FROM curriculum 
			  WHERE level_id = ?
			  AND subject_id NOT IN (SELECT subject_id FROM student_subjects WHERE enrollment_id = ? AND ay_id = ?)
			  ';
			  
	  $result = $this->db->query($sql, array($level_id,$enrollment_id,$this->c_sy->id))->result();
	  
	  if(!empty($result))
	  {

	  	$ctr = 0;
			foreach($result as $key => $r){

				unset($data);
				$data['subject_id'] = $r->subject_id;
				$data['enrollment_id'] = $enrollment_id;
				$data['ay_id'] = $this->c_sy->id;
				if($this->insert($data)['status']){
					$ctr++;
						
					$this->load->model('M_student_grades');
					$this->M_student_grades->generate_grade_by_subject($enrollment_id, $r->subject_id);
				}
			}

			return $ctr>0?TRUE:FALSE;
	  }

	  return false;
	}
	
	public function get_all_by_enrollment_id($enrollment_id)
	{
	  $sql = "SELECT subjects.subj_code, subjects.subj_desc,
					 concat(schedule.time_from,' - ',schedule.time_to) as time,
					 schedule.day
            FROM student_subjects
            LEFT JOIN subjects ON subjects.subj_id = student_subjects.subject_id
			LEFT JOIN schedule ON schedule.subject_id = student_subjects.subject_id
            WHERE student_subjects.enrollment_id = ?
			GROUP BY subjects.subj_code
			ORDER BY schedule.time_from ASC";
	  
	  $query = $this->db->query($sql, $enrollment_id);
	  return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function all_by_enrollment_id($enrollment_id)
	{
	  $sql = "SELECT *
            FROM student_subjects
            WHERE student_subjects.enrollment_id = ?
			AND student_subjects.ay_id = ?";
	  
	  $query = $this->db->query($sql,array($enrollment_id,$this->c_sy->id));
	  return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_all_students_in_subject($subj_id)
	{
		$sql = "SELECT 	enrollments.e_id,
						profiles.lastname,
						profiles.firstname,
						profiles.middlename
				FROM enrolled_subjects
				JOIN enrollments ON enrolled_subjects.enrollment_id = enrollments.e_id
				JOIN profiles ON enrollments.e_profile_id = profiles.profile_id
				WHERE enrolled_subjects.subj_id =  ?";

		$query = $this->db->query($sql, array($subj_id));
		return $query->num_rows > 0 ? $query->result() : FALSE;
	}
	
	public function get_all_subjects_in_student($enrollment_id)
	{
		$sql = "SELECT 	subjects.subj_id,
						subjects.subj_code,
						subjects.subj_desc
				FROM enrollments
				JOIN student_subjects ON enrollments.e_id = student_subjects.enrollment_id
				JOIN subjects ON subjects.subj_id = student_subjects.subject_id
				WHERE enrollments.e_id = ?";
		$query = $this->db->query($sql, array($enrollment_id));
		return $query->num_rows() > 0 ? $query->result() : FALSE;
	}
	
	public function get_all_schedule_by_student_subjects($enroll_id = false)
	{	
		$select = "SELECT sub.subj_code,
						  sub.subj_desc,
						  concat(sch.time_from,' ',sch.time_to) as time,
						  sch.day,
						  concat(emp.first_name,' ',emp.last_name) as teacher_name
				   FROM student_subjects ss
				   LEFT JOIN enrollments e ON ss.enrollment_id = e.e_id
				   LEFT JOIN subjects sub ON sub.subj_id = ss.subject_id
				   LEFT JOIN schedule sch ON sch.subject_id = sub.subj_id AND sch.section_id = e.e_section_id
				   LEFT JOIN employees emp ON sch.teacher_id = emp.emp_id
				   WHERE ss.enrollment_id = ?";
				   
		$q = $this->db->query($select,array($enroll_id));
		return $q->num_rows() >= 1 ? $q->result() : FALSE;
	}

	//parem enrollment_id and Level Id
	public function generate_student_subject_children($e_id,$l_id,$sec_id)
	{
		if($sec_id > 0)
		{
			$CI =& get_instance();
			$CI->load->model('M_curriculum','mc');
			$CI->load->model('M_gperiods','mg');
			$total_gp = count($CI->mg->get_gperiods());
			
			//get all grading periods
			$gp = $CI->mg->get_gperiods();

			//get all subjects in curricuklum
			$curriculum = $CI->mc->get_selected_subjects_for_curriculum($l_id);

			if($curriculum)
			{
				foreach($curriculum as $k => $v)
				{
					$subj[$k] = $v->subject_id;
				}

				if($subj)
				{
					//get all subject ID
					$subject_ids = "'".implode("','",$subj)."'";
					
					//get all children from subjects
					$get_subject_children = "SELECT subj_id,children FROM subjects WHERE subj_id IN ($subject_ids) AND children != ''";
					$result = $this->db->query($get_subject_children);

					if($result)
					{
						foreach ($result->result() as $key => $value)
						{
							$sc1[$value->subj_id] = array_filter(explode(';',$value->children),'strlen');
						}

						//if there is children
						if(isset($sc1))
						{
							$x = 0;
							//loop through the grading periods
							foreach ($gp as $gpk => $gpv)
							{
								foreach($sc1 as $sc1k => $sc1v)
								{
									//create array for subject children
									foreach($sc1v as $sc1vk => $sc1vv)
									{
										$data[$x]['subject_name'] = trim($sc1vv);
										$data[$x]['subject_parent'] = $sc1k;
										$data[$x]['ay_id'] = $this->c_sy->id;
										$data[$x]['section_id'] = $sec_id;
										$data[$x]['e_id'] = $e_id;
										$data[$x]['gp_id'] = $gpv->gp_id;
										$x++;
									}
								}
							}

							//final
							if($data)
							{
								//check if data already exists
								//
								$where['ay_id'] = $this->c_sy->id;
								$where['e_id'] = $e_id;
								$where['section_id'] = $sec_id;

								$ch = $this->db->where($where)->get('subject_children');

								if($ch->num_rows() >=1)
								{
									//if entry already exist delete the current data then add the new data
									$this->db->where($where)->delete('subject_children');
								}

								$this->db->insert_batch('subject_children', $data);
								return $this->db->affected_rows() >=1 ? TRUE : FALSE;
							}else{
								return FALSE;
							}
						}else{
							//no children assigned for subject jsut return true
							return TRUE;
						}
					}else{
						//unable to get children or no children
						return FALSE;
					}
				}else{
					//unable to get subject from curricilum
					return FALSE;
				}
			}else{
				return FALSE;
			}
		}
	}
}
?>
